import logging
import datetime
import csv
import os
from bank_manage_system.database.db_connector import DatabaseConnector

logger = logging.getLogger(__name__)


class ReportManager:
    def __init__(self, db, user_id="admin"):
        self.db = db
        self.user_id = user_id
        self.current_report_data = []
        self.current_headers = []
        self.current_report_type = ""

    def execute_sql_query(self, query, params=None):
        try:
            logger.debug(f"执行SQL查询: {query[:100] + ('...' if len(query) > 100 else '')}")
            cursor = self.db.connection.cursor()

            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)

            results = cursor.fetchall()
            logger.debug(f"查询成功: 返回 {len(results)} 条记录")
            cursor.close()
            return results
        except Exception as e:
            logger.error(f"SQL查询失败: {str(e)}")
            return []

    def format_time(self, raw_time):
        if isinstance(raw_time, datetime.datetime):
            return raw_time.strftime("%Y-%m-%d %H:%M:%S")
        elif isinstance(raw_time, str):
            try:
                return datetime.datetime.strptime(
                    raw_time, "%Y-%m-%d %H:%M:%S"
                ).strftime("%Y-%m-%d %H:%M:%S")
            except:
                return raw_time
        else:
            return str(raw_time)

    def generate_transaction_report(self, start_date, end_date):
        query = f"""
        SELECT 
            t.tradeID, 
            t.cardID, 
            COALESCE(u.customerName, '系统账户') AS customer_name,
            t.tradeDate AS raw_time,
            t.tradeType, 
            t.tradeMoney, 
            COALESCE(t.targetCard, ''),
            COALESCE(t.remark, '')
        FROM tradeinfo t
        LEFT JOIN cardinfo c ON t.cardID = c.cardID
        LEFT JOIN userinfo u ON c.customerID = u.customerID
        WHERE t.tradeDate BETWEEN '{start_date} 00:00:00' AND '{end_date} 23:59:59'
        ORDER BY t.tradeDate DESC
        """

        results = self.execute_sql_query(query)

        processed_results = []
        for row in results:
            raw_time = row[3]
            formatted_time = self.format_time(raw_time)
            new_row = (
                row[0], row[1], row[2],
                formatted_time,
                row[4], row[5], row[6], row[7]
            )
            processed_results.append(new_row)

        self.current_headers = [
            "交易ID", "卡号", "客户姓名",
            "交易时间", "交易类型", "金额", "对方卡号", "备注"
        ]
        return self.current_headers, processed_results

    def generate_loss_report(self, start_date, end_date):
        query = f"""
        SELECT 
            l.logID,
            u.customerID,
            u.customerName,
            l.operationTime AS raw_time,
            l.operationType,
            l.cardID
        FROM transactionlog l
        JOIN userinfo u ON l.customerID = u.customerID
        WHERE l.operationType IN ('挂失', '解挂')
        AND l.operationTime BETWEEN '{start_date} 00:00:00' AND '{end_date} 23:59:59'
        ORDER BY l.operationTime DESC
        """

        results = self.execute_sql_query(query)

        processed_results = []
        for row in results:
            raw_time = row[3]
            formatted_time = self.format_time(raw_time)
            new_row = (
                row[0], row[1], row[2],
                formatted_time,
                row[4], row[5]
            )
            processed_results.append(new_row)

        self.current_headers = [
            "日志ID", "客户ID", "客户姓名",
            "操作时间", "操作类型", "卡号"
        ]
        return self.current_headers, processed_results

    def generate_monthly_statistics(self):
        query = """
        SELECT 
            DATE_FORMAT(tradeDate, '%Y-%m') AS month,
            COUNT(*) AS transaction_count,
            ROUND(SUM(CASE WHEN tradeType = '存入' THEN tradeMoney ELSE 0 END), 2) AS total_deposit,
            ROUND(SUM(CASE WHEN tradeType = '支取' THEN ABS(tradeMoney) ELSE 0 END), 2) AS total_withdraw,
            ROUND(SUM(CASE WHEN tradeType IN ('转账', '转账出') THEN ABS(tradeMoney) ELSE 0 END), 2) AS total_transfer_out,
            ROUND(SUM(CASE WHEN tradeType = '转账入' THEN tradeMoney ELSE 0 END), 2) AS total_transfer_in,
            COUNT(CASE WHEN tradeType LIKE '转账%%' THEN 1 END) AS transfer_count
        FROM tradeinfo
        GROUP BY DATE_FORMAT(tradeDate,'%Y-%m')
        ORDER BY month DESC
        """

        results = self.execute_sql_query(query)

        self.current_headers = [
            "月份", "交易笔数", "总存入额",
            "总支取额", "转出总额", "转入总额", "转账笔数"
        ]
        return self.current_headers, results

    def generate_customer_summary_report(self):
        query = """
        SELECT 
            u.customerID,
            u.customerName,
            COUNT(DISTINCT c.cardID) AS account_count,
            ROUND(COALESCE(SUM(c.balance), 0), 2) AS total_balance,
            MAX(c.openDate) AS last_open_date,
            MAX(CASE WHEN c.IsReportLoss = 1 THEN '是' ELSE '否' END) AS has_loss_report
        FROM userinfo u
        LEFT JOIN cardinfo c ON u.customerID = c.customerID
        GROUP BY u.customerID, u.customerName
        ORDER BY total_balance DESC
        """

        results = self.execute_sql_query(query)

        self.current_headers = [
            "客户ID", "客户姓名", "账户数量",
            "总余额", "最近开卡日期", "是否有挂失"
        ]
        return self.current_headers, results

    def generate_deposit_distribution(self):
        query = """
        SELECT 
            savingID AS deposit_type,
            COUNT(*) AS account_count,
            ROUND(SUM(balance), 2) AS total_balance,
            ROUND(AVG(balance), 2) AS avg_balance
        FROM cardinfo
        GROUP BY savingID
        ORDER BY total_balance DESC
        """

        results = self.execute_sql_query(query)

        self.current_headers = [
            "存款类型", "账户数量", "总余额", "平均余额"
        ]
        return self.current_headers, results

    def export_csv(self, file_path):
        try:
            with open(file_path, 'w', newline='', encoding='utf-8-sig') as csvfile:
                csvwriter = csv.writer(csvfile)
                csvwriter.writerow(self.current_headers)

                for row_data in self.current_report_data:
                    processed_row = []
                    for value in row_data:
                        if isinstance(value, str) and value.replace(',', '').replace('.', '').isdigit():
                            value = value.replace(',', '')
                        processed_row.append(value)
                    csvwriter.writerow(processed_row)

            return True, file_path
        except Exception as e:
            logger.error(f"导出CSV失败: {str(e)}")
            return False, str(e)